-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:视图依赖自定义函数，重建的函数参数类型和原始函数不一样，部分合理报错
--step1:创建 products 表;expect:成功
drop table if exists products;
CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100),
 price DECIMAL(10, 2)
);

--step2:插入数据;expect:成功
INSERT INTO products (product_name, price) VALUES
('Product A', 100.00),
('Product B', 200.00),
('Product C', 300.00);
--step3:创建自定义函数;expect:成功
drop function if exists func_ddl0029;
CREATE FUNCTION func_ddl0029(p_hire_date date) RETURNS integer
AS $$
DECLARE
 v_working_years integer;
 v_current_date date := current_date;
CREATE FUNCTION calculate_discounted_price(
 original_price DECIMAL(10, 2),
 discount_rate DECIMAL(3, 2)
)
RETURNS DECIMAL(10, 2) AS $$
BEGIN
 RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;
--step4:创建视图，引用自定义函数;expect:成功
drop view if exists product_discount_view;
CREATE VIEW product_discount_view AS
SELECT
 product_id,
 product_name,
 price AS original_price,
 calculate_discounted_price(price, 0.1) AS discounted_price
FROM
 products;
--step5:查询视图;expect:成功
select * from product_discount_view;
--step6:删除函数;expect:成功
drop FUNCTION calculate_discounted_price;
--step7:查询视图;expect:报错
select * from product_discount_view;
--step8:查询视图状态;expect:无效
select valid from pg_object where object_oid='product_discount_view'::regclass;
--step9:重建函数，参数类型不一样且和原始函数参数类型可转换;expect:成功
CREATE FUNCTION calculate_discounted_price(
 original_price number(10, 2),
 discount_rate number(3, 2)
)
RETURNS DECIMAL(10, 2) AS $$
BEGIN
 RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;
--step10:查询视图状态;expect:无效
select valid from pg_object where object_oid='product_discount_view'::regclass;
--step11:查询视图;expect:成功
select * from product_discount_view;
--step12:查询视图状态;expect:有效
select valid from pg_object where object_oid='product_discount_view'::regclass;
--step13:查询视图定义;expect:成功
select pg_get_viewdef('product_discount_view');
--step14:清理环境;expect:成功
drop table products;
drop function calculate_discounted_price;
drop view product_discount_view;